package com.servlet.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import DB.DBManager;

import com.servlet.vo.ConsumDetail;

public class ConsumDetailDao {

	private Connection con;
	private Statement statement;
	private ResultSet resultSet;
	private boolean flag = false;
	private StringBuffer sql = new StringBuffer();
	
	public ConsumDetailDao() throws SQLException{
		con = DBManager.getConn();
		statement = con.createStatement();
	}
	
	public void close(){
		
		if(null != resultSet){
			try {
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(null != statement){
			try {
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(null != con){
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public boolean save(ConsumDetail obj) {
		
		sql.append("INSERT INTO ConsumDetail(ADDTIME,deleteStatus,capitalType,user_id,enter_id,consumptionType_id,payment_id,detail,consumptionAmount) ");
		sql.append("VALUES( NOW(),FALSE,'");
		sql.append(obj.getCapitalType() + "','" + obj.getUser_id() + "','"+obj.getEnter_id() + "','"+obj.getConsumptionType_id()
				+ "','"+obj.getPayment_id() + "','"+obj.getDetail() + "',"+obj.getConsumptionAmount() + ")");
		
		try {
			flag = statement.execute(sql.toString());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			flag = true;
		}finally{
			close();
		}
		return flag;
	}
	
	public List<ConsumDetail> getList(int startIndex, int pageSize){
		
		sql.append("SELECT a.id,a.addTime,a.capitalType,a.user_id,a.enter_id,a.ConsumptionType_id,a.payment_id,a.detail,a.consumptionAmount," +
				"b.userName userName, c.userName enterName,d.consumptionTypeName,e.paymentName " +
				"FROM consumdetail a " +
				"LEFT JOIN USER b ON a.user_id = b.id " +
				"LEFT JOIN USER c ON a.enter_id = c.id " +
				"LEFT JOIN consumptiontype d ON a.consumptionType_id = d.id " +
				"LEFT JOIN payment e ON a.payment_id = e.id " +
				"order by a.addTime desc limit "+ startIndex+","+pageSize);
		List<ConsumDetail> objs = new ArrayList<ConsumDetail>();
		try {
			resultSet = statement.executeQuery(sql.toString());
			ConsumDetail obj = null;
			while(resultSet.next()){
			
				obj = new ConsumDetail();
				obj.setId(resultSet.getLong("id"));
				obj.setAddTime(resultSet.getTimestamp("addTime"));
				obj.setCapitalType(resultSet.getInt("capitalType"));
				obj.setUser_id(resultSet.getInt("user_id"));
				obj.setEnter_id(resultSet.getInt("enter_id"));
				obj.setConsumptionType_id(resultSet.getInt("consumptionType_id"));
				obj.setPayment_id(resultSet.getInt("payment_id"));
				obj.setDetail(resultSet.getString("detail"));
				obj.setConsumptionAmount(resultSet.getBigDecimal("consumptionAmount"));
				obj.setUserName(resultSet.getString("userName"));
				obj.setEnterName(resultSet.getString("enterName"));
				obj.setConsumptionTypeName(resultSet.getString("consumptionTypeName"));
				obj.setPaymentName(resultSet.getString("paymentName"));
				objs.add(obj);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}
		return objs;
	}
	
}
